﻿using Flurl.Http;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using YunShop_WebAPI.Model;

namespace YunShop_WebAPI.BLL
{
    /// <summary>
    /// 小程序商城业务
    /// </summary>
    public static class MallCommon
    {
        /// <summary>
        /// 商品详情
        /// </summary>
        public static Hashtable GetGoodsDetail(string shop_appid, Int64 goods_id)
        {
            int mCorpID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.CorpID);
            Int64 mUserID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.UserID);
            string connstr = SystemInfo.GetClusterConn(shop_appid);
            //string msql = @"EXEC pr_wap_dh_GetGoodsDetail @mCorpID,@mUserID,@mGoodsID";
            string msql = @"EXEC pr_wap_GetGoodDetail @mCorpID,@FItemID";
            SqlParameterList par_list = new SqlParameterList();
            par_list.Add("@mCorpID", SqlDbType.BigInt, mCorpID);
            //par_list.Add("@mUserID", SqlDbType.BigInt, mUserID);
            par_list.Add("@FItemID", SqlDbType.BigInt, goods_id);
            ArrayList datalist = DBHelper.Select(msql, par_list.Get_List(), connstr);
            Hashtable ht_result = new Hashtable();
            if (datalist != null && datalist.Count > 0)
            {
                ht_result = datalist[0] as Hashtable;
                var itemUrl = CommonFun.ConvertToString(ht_result["FProImage"]);
                ArrayList list_img = new ArrayList();
                if (!string.IsNullOrWhiteSpace(itemUrl))
                {
                    var arr = itemUrl.Replace("$$", "$").Split('$');
                    foreach (var str in arr)
                    {
                        if (!string.IsNullOrWhiteSpace(str))
                        {
                            Hashtable ht_img = new Hashtable();
                            ht_img["FGoodImg"] = str;
                            list_img.Add(ht_img);
                        }
                    }
                }
                ht_result["FProImage"] = list_img;
            }
            return ht_result;
        }


        /// <summary>
        /// 商城小程序用户登录Chris Lee
        /// </summary>
        /// <param name="appid"></param>
        /// <param name="code"></param>
        /// <param name="status"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public static ArrayList OnUserLogin(string appid, string code, ref int status, ref string message, int CorpId, long FStockID, int FParentID)
        {
            var appModel = new SystemAppModel();
            string sDbSplit = "";
            //appModel.FHostIP = GetLoginClusterIP(ref sMasterFlag, ref sDbSplit);
            string connstr = string.Format(DBHelper.ConnCluster, appModel.FHostIP, sDbSplit);
            string openid = "";
            string unionid = "";

            if (!GetUserInfo(appid, code, ref openid, ref unionid, ref message, CorpId, DBHelper.ConnMaster))
            {
                message = "code解析失败";
                return null;
            }

            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT  [FItemID],[FOpenID],[FCorpID],[FUserID],[FCreateTime],[FLoginTime],[FPhone],[FUnionID],[FNiName],[FGender],[FCity],[FProvince],[FCountry],[FAvatarUrl],[FHostID],[FSettingID],[FStockID],[FMemberID]  FROM [KingCloudShop].[dbo].[t_UserWeixin] where [FUnionID] = @FUnionID   ");
            if (CorpId > 0)
            {
                sql.Append(" and FCorpID = " + CorpId);
            }
            else
            {
                sql.Append(" and FCorpID = " + 52);
            }


            List<SqlParameter> ltpara = new List<SqlParameter>
                        {
                            new SqlParameter("@FUnionID", unionid)
                        };
            ArrayList userWx = DBHelper.Select(sql.ToString(), ltpara.ToArray(), DBHelper.ConnMaster);
            if (userWx.Count is 0)
            {
                return new ArrayList();
            }


            Hashtable userWxInfo = userWx[0] as Hashtable;

            //Dictionary<string, object> userWxInfo = JsonConvert.DeserializeObject<Dictionary<string, object>>(JsonConvert.SerializeObject(userWxInfoO));
            string m_FPhone = userWxInfo["FPhone"] as string;
            int? m_FUserID = userWxInfo["FUserID"] as int?;
            long? m_FWeixinId = userWxInfo["FItemID"] as long?;
            long m_CorpId = CommonFun.ConvertToInt(userWxInfo["FCorpID"]);


            Hashtable ht = new Hashtable();

            status = 1;
            message = "登录成功";
            appModel.FCorpID = (int)m_CorpId;
            appModel.FUserID = m_FUserID == null ? 0 : (int)m_FUserID;   // 用户ID
            appModel.FStockID = FStockID;
            appModel.FOwnerID = 0; //用户组ID
            appModel.FParentID = FParentID;
            appModel.FWeixinID = m_FWeixinId.Value;

            string msql = @"SELECT FDbHostIP,FDbSplit,FDbHostNetIP FROM t_CorpHost where FMinCorpID <= {0} and FMaxCorpID >= {0}";
            if (appModel.FParentID > 0)
                msql = string.Format(msql, appModel.FParentID);
            else msql = string.Format(msql, appModel.FCorpID);

            ArrayList dataAll = DBHelper.Select(msql, DBHelper.ConnMaster);
            if (dataAll.Count > 0)
            {
                Hashtable record = (Hashtable)dataAll[0];
                appModel.FHostIP = CommonFun.ConvertToString(record["FDbHostIP"]);
                appModel.FDbSplit = CommonFun.ConvertToString(record["FDbSplit"]);
                if (DBHelper.DebugFlag) appModel.FHostIP = CommonFun.ConvertToString(record["FDbHostNetIP"]);
            }



            string sSystemAppID = SystemInfo.SetSystemAppID(appModel);
            ht["shop_appid"] = 12;
            ht["systemAppId"] = sSystemAppID;

            //用户信息
            ht["userInfo"] = new Dictionary<string, object>() {
                { "openId",openid},
                { "unionid",unionid},
                { "FWeixinID",m_FWeixinId},
                { "phone",m_FPhone}
            };

            // 返回用户信息
            //ht["dt_user"] = DBHelper.Select("select * from t_UserWeixin where FItemID = @FWeixinID", new List<SqlParameter>() {
            //                        new SqlParameter("@FWeixinID", (userWx[0] as Hashtable)["FItemID"]),
            //                }.ToArray(), DBHelper.ConnMaster)[0];


            ht["user"] = userWxInfo;
            if (CommonFun.ConvertToBigInt(userWxInfo["FMemberID"]) > 0)
            {
         

                // 返回会员信息
                ht["member"] = DBHelper.Select(" select a.FItemID as MemberId,b.FNumber +' '+ b.FName as CorpName,c.FName as ClassName,a.FName as NiName,a.FNumber as Number,   a.FPoint as Ponit,a.FAmount as Amount,d.FSex as FSex,convert(varchar(10),d.FBirthday,120) as FBirthday, c.FTypeID,a.FDiscount,d.FEmail,convert(varchar(10),d.FEndTime,120) as FEndTime,d.FAddress     from t_Member a left join t_MemberDetail d on d.FItemID=a.FItemID left join  t_MemberClass c on a.FClassID=c.FItemID left join  t_Corpration b on a.FCorpID=b.FItemID    where a.FItemID = @FMemberID", new List<SqlParameter>() {
                                    new SqlParameter("@FMemberID", userWxInfo["FMemberID"]),
                            }.ToArray(), DBHelper.ConnMaster)[0];
            }

            if (CommonFun.ConvertToBigInt(userWxInfo["FStockID"]) > 0)
            {

                // 返回仓库信息
                ht["stock"] = DBHelper.Select("select a.*,c.FNumber +' '+c.FName as FCorpName,1 as FStatus   from t_Stock a inner join t_Corpration c on a.FCorpID = c.FItemID where a.FItemID = @FStockID", new List<SqlParameter>() {
                                    new SqlParameter("@FStockID", userWxInfo["FStockID"]),
                            }.ToArray(), DBHelper.ConnMaster)[0];
            }


            /* // 门店信息
             ht["store"] = new Dictionary<string, object>()
             {
                     { "CorpID",m_CorpId},
                     //{ "hasBindStore",m_CorpId>0}
             };*/

            //ht["token"] = "token";
            return Utils.HashtableToArrayList(ht);

        }

        /// <summary>
        /// 小程序用户登录 Chris Lee
        /// </summary>
        public static bool GetUserInfo(string appid, string code,
            ref string openId, ref string unionid, ref string message, int CorpId, string connstr)
        {
            try
            {
                var secret = GetAppSecret(appid);
                if (string.IsNullOrEmpty(secret))
                {
                    message = "secret不能为空";
                    return false;
                }

                //获取openId

                JObject jo = WxService.GetOpenIdByCode(code, appid, secret);
                unionid = CommonFun.GetJObjectValue(jo, "unionid");
                openId = CommonFun.GetJObjectValue(jo, "openid");//orPaa5eBqlpEauuYCjEKPMnxpMa0

                //if (string.IsNullOrEmpty(unionId) && string.IsNullOrEmpty(openId))
                if (string.IsNullOrEmpty(openId))
                {
                    message = "code解析失败";
                    return false;
                }

                // 判断用户是否存在，如果不存在则插入

                string sql = @"SELECT FNiName FROM dbo.t_UserWeixin  where FUnionID=@FUnionID and FCorpID=@FCorpID";
                SqlParameterList parlist = new SqlParameterList();
                parlist.Add("@FUnionID", SqlDbType.VarChar, unionid);
                parlist.Add("@FCorpID", SqlDbType.VarChar, CorpId > 0 ? CorpId : 52);
                DataTable dt = DBHelper.GetDataTable(sql, parlist.Get_List(), connstr);

                if (dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[index: 0];
                    return true;
                }
                else
                {
                    // 查询公司stockID
                    ArrayList arr = DBHelper.Select("select * from   t_Corpration where FItemID= @CorpId", new List<SqlParameter>() { new SqlParameter("@CorpId", CorpId > 0 ? CorpId : 52) }.ToArray(), DBHelper.ConnMaster);

                    sql = @"insert into t_UserWeixin(FOpenID,FCreateTime,FNiName,FCorpID,FUnionID,FStockID) values(@FOpenID,@FCreateTime,0,@CorpId,@FUnionID,@FStockID)";
                    //@FUnionID,@FNiName,@FAvatarUrl,
                    SqlParameterList par_list = new SqlParameterList();
                    par_list.Add("@FOpenID", SqlDbType.VarChar, openId);
                    par_list.Add("@FUnionID", SqlDbType.VarChar, unionid);
                    par_list.Add("@FCreateTime", SqlDbType.DateTime2, DateTime.Now);
                    //par_list.Add("@FUnionID", SqlDbType.VarChar, unionId);
                    par_list.Add("@FNiName", SqlDbType.VarChar, "李");
                    par_list.Add("@CorpId", SqlDbType.Int, CorpId > 0 ? CorpId : 52);
                    par_list.Add("@FStockID", SqlDbType.BigInt, (arr[0] as Hashtable)["FStockID"]);

                    //par_list.Add("@FAvatarUrl", SqlDbType.VarChar, openId);
                    string msg = DBHelper.ExeSQL(sql, par_list.Get_List(), connstr);
                    return true;
                }
            }
            catch (Exception ex)
            {
                message = ex.Message;
                return false;
            }
        }

        /// <summary>
        /// 通过appid获取密钥
        /// </summary>
        /// <param name="appid"></param>
        /// <returns></returns>
        public static string GetAppSecret(string appid)
        {
            string sql = @"select FAppSecret from t_MiniSetting where FAppID=@FAppID";
            SqlParameterList parlist = new SqlParameterList();
            parlist.Add("@FAppID", SqlDbType.VarChar, appid);
            DataTable dt = DBHelper.GetDataTable(sql, parlist.Get_List(), DBHelper.ConnMaster);

            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                return CommonFun.ConvertToString(dr["FAppSecret"]);
            }
            return "";
        }


        /// <summary>
        /// 商品分类列表
        /// </summary>
        public static ArrayList GetGoodClassList(string shop_appid)
        {
            int mCorpID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.CorpID);
            int mPtCorpID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.TopCorpID);
            string connstr = SystemInfo.GetClusterConn(shop_appid);
            DataTable dt = GetGoodClassData(mCorpID, mPtCorpID, DBHelper.ConnMaster);
            string str_filer = "FParentID = 0";
            DataRow[] drs_top = dt.Select(str_filer);
            if (drs_top.Length == 0) return null;

            DataTable dt_top = drs_top.CopyToDataTable();
            ArrayList list_top = DBHelper.DataTable2ArrayList(dt_top);
            ArrayList datalist = new ArrayList();
            foreach (Hashtable row in list_top)
            {
                str_filer = string.Format("FParentID = {0}", CommonFun.ConvertToBigInt(row["FItemID"]));
                DataRow[] drs_children = dt.Select(str_filer);
                ArrayList list_children = new ArrayList();
                if (drs_children.Length > 0)
                {
                    DataTable dt_children = drs_children.CopyToDataTable();
                    list_children = DBHelper.DataTable2ArrayList(dt_children);
                }
                row["children"] = list_children;
                datalist.Add(row);
            }

            return datalist;
        }

        /// <summary>
        /// 商品分类列表
        /// </summary>
        public static DataTable GetGoodClassData(int mCorpID, int mPtCorpID, string connstr)
        {
            string msql = @"SELECT FItemID, FName, FIcon, FParentID FROM  dbo.t_GoodsClass "
                    + "where (FCorpID = @mCorpID or (FCorpID = @mParentID and @mParentID > 0)) and FDeleted  > 0";
            SqlParameterList par_list = new SqlParameterList();
            par_list.Add("@mParentID", SqlDbType.Int, mPtCorpID);
            par_list.Add("@mCorpID", SqlDbType.BigInt, mCorpID);
            DataTable dt = DBHelper.GetDataTable(msql, par_list.Get_List(), connstr);
            return dt;
        }
        /// <summary>
        /// 删除购物车商品
        /// </summary>
        public static int DelShoppingCart(string shop_appid, ArrayList detailed, ref string msg)
        {
            int mCorpID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.CorpID);
            Int64 mUserID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.UserID);
            string connstr = SystemInfo.GetClusterConn(shop_appid);
            string msql = @"Delete t_UserShopping where FUserID = @mUserID and FGoodsID = @FGoodsID";
            SqlConnection con = new SqlConnection(connstr);
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            try
            {
                foreach (Hashtable row in detailed)
                {
                    row["mUserID"] = mUserID;
                    SqlCommand cmd = new SqlCommand(msql, con);
                    cmd.Connection = con;
                    cmd.Transaction = tran;
                    cmd.Parameters.Clear();
                    if (row != null) DBHelper.SetArgs(msql, row, cmd);
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                msg = "删除成功";
            }
            catch (SqlException ex)
            {
                tran.Rollback();
                msg = ex.Message.Replace("\r\n", "");
                return 0;
            }
            finally
            {
                con.Close();
                con = null;
            }
            return 1;
        }


        //保存扫码支付订单SaveIPay
        public static string SaveRenewalsOrder(int FCorpID, int FUserID, decimal Money, int mYears, ref string msg, int type, string shop_appid, Dictionary<string, object> dic = null)
        {
            try
            {
                int mCorpID = (int)SystemInfo.GetSystemAppID(shop_appid, SystemAppID.TopCorpID);
                string connstr = SystemInfo.GetClusterConn(shop_appid);
                long FItemId = CommonFun.ConvertToBigInt(dic["FItemID"]);
                string number = dic["number"] as string;
                string BillNo = GetMobilePayClientSn(number);
                string sql = @"insert into y_Mall_PayOrder(MInterID,FBillNo,FMoney,FProductName,FCreateTime"
                        + " ,FPayState )values(@MInterID,@BillNo,@Money,"
                        + "@FProductName,getdate(),0)";
                SqlParameterList par_list = new SqlParameterList();
                par_list.Add("@MInterID", SqlDbType.BigInt, FItemId);
                par_list.Add("@BillNo", SqlDbType.VarChar, BillNo);
                par_list.Add("@Money", SqlDbType.Decimal, Money);
                par_list.Add("@FProductName", SqlDbType.VarChar, "");
                DBHelper.ExeSQL(sql, par_list.Get_List(), connstr);
                return BillNo;
            }
            catch (Exception e)
            {
                msg = e.Message;
                return "";
            }
        }

        /// <summary>  
        /// 获取时间戳  
        /// </summary>  
        /// <returns></returns>  
        public static Int64 GetTimeStampMS()
        {
            TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.Milliseconds);
        }

        public static string GetMobilePayClientSn(string sBillNo)
        {
            string mBillNo = sBillNo + "-" + GetTimeStampMS();

            if (mBillNo.Length > 32)
            {
                mBillNo = mBillNo.Substring(mBillNo.Length - 32);
            }

            return mBillNo;
        }

        /// <summary>
        /// 定时轮询查单ArrayList rows, int FCorpID, int FUserID,decimal Money,ref string msg
        /// </summary>
        public static void SetRepeatQueryPay(ArrayList rows, string order_no, int business_id, int FYears, int type, string shop_appid)
        {
            //支付的订单类型，1：商城订单，1：从订单列表来的商城订单,11：会员充值
            Thread newThread = new Thread(() =>
            SetRepeatQueryPay_Thread(rows, order_no, business_id, FYears, type, shop_appid), 1024 * 512);
            newThread.Name = String.Format("Thread{0}", order_no);
            newThread.Start();
        }
        /// <summary>
        /// 定时轮询查单
        /// </summary>
        public static void SetRepeatQueryPay_Thread(ArrayList rows, string order_no, int business_id, int FYears, int type, string shop_appid)
        {
            bool bLoop = true;
            int mSleepTime = 10000;
            int mTimCount = 0;
            try
            {
                Thread.Sleep(mSleepTime);
                while (bLoop)
                {
                    Hashtable respData = new Hashtable();
                    bool bResult = SxfPayCommon.Query(order_no, business_id, ref respData);
                    if (bResult)
                    {
                        //  支付成功
                        string m_sql = @"select * from y_Mall_PayOrder where FBillNo= @FBillNo";
                        List<SqlParameter> m_params = new List<SqlParameter>() {
                        new SqlParameter("@FBillNo",order_no)
                        };
                        ArrayList m_PayOrders = DBHelper.Select(m_sql, m_params.ToArray(), DBHelper.ConnMaster);
                        //SystemInfo.writeServiceLog("m_PayOrders", JsonConvert.SerializeObject(m_PayOrders), "11", "AA", DBHelper.ConnMaster);
                        if (m_PayOrders.Count > 0)
                        {
                            Hashtable m_Pay_Order = m_PayOrders[0] as Hashtable;
                            long m_MInterID = CommonFun.ConvertToBigInt(m_Pay_Order["MInterID"]);

                            DateTime.TryParseExact(CommonFun.ConvertToString(respData["payTime"]), "yyyyMMddHHmmss", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime m_PayTime);
                            m_sql = @"UPDATE [dbo].[y_Mall_PayOrder] SET  [FPayTime] = @FPayTime, [FPayState] = 1 where [FBillNo]= @FBillNo ";
                            m_params = new List<SqlParameter>() {
                                    new SqlParameter("@FPayTime",m_PayTime),
                                    new SqlParameter("@FBillNo",order_no),
                                    };
                            DBHelper.ExeSQL(m_sql, m_params.ToArray(), DBHelper.ConnMaster);
                            //SystemInfo.writeServiceLog(" UPDATE y_Mall_PayOrder", JsonConvert.SerializeObject(respData), "22", "BB", DBHelper.ConnMaster);
                            m_sql = @"UPDATE [dbo].[y_Mall_Master] SET [FStateID] = 1,[FPayDate]=@FPayDate,[FPayFlag] = 1,[FPayType] = 1 where [KInterID]= @KInterID ";
                            m_params = new List<SqlParameter>() {
                                    new SqlParameter("@KInterID",m_MInterID),
                                    new SqlParameter("@FPayDate",m_PayTime),
                                    };
                            DBHelper.ExeSQL(m_sql, m_params.ToArray(), DBHelper.ConnMaster);
                            //SystemInfo.writeServiceLog("UPDATE y_Mall_Master", JsonConvert.SerializeObject(m_PayOrders), "33", "CC", DBHelper.ConnMaster);
                        }

                    }
                    if (mTimCount > 12) bLoop = false;
                    mTimCount += 1;
                    Thread.Sleep(mSleepTime);
                }
            }
            catch (Exception err)
            {

            }
        }


        public static bool PaySMS(string shop_appid, string BillNo, string payID)
        {
            try
            {
                int mCorpID = SystemInfo.GetSystemAppID(shop_appid, SystemAppID.CorpID);
                string connstr = SystemInfo.GetClusterConn(shop_appid);
                string sql = @"exec pr_bi_RechargeSMS @FBillNo,@FPayID";
                SqlParameterList par_list = new SqlParameterList();
                par_list.Add("@FBillNo", SqlDbType.VarChar, BillNo);
                par_list.Add("@FPayID", SqlDbType.VarChar, payID);
                DataTable dt = DBHelper.GetDataTable(sql, par_list.Get_List(), connstr);

                if (dt == null)
                    return false;
                if (dt.Rows.Count == 0)
                    return false;
                if (CommonFun.ConvertToInt(dt.Rows[0]["retvalue"]) > 0)
                    return true;
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        /// <summary>
        /// 删除购物车
        /// </summary>
        /// <returns></returns>
        public static bool DelCart(long FWeixinID, long FCorpID)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> ltpara = new List<SqlParameter>();
            strSql.Append(@"Delete from y_Mall_Shopping where FWeixinID = @FWeixinID and FCorpID = @FCorpID");
            ltpara.Add(new SqlParameter("@FWeixinID", FWeixinID));
            ltpara.Add(new SqlParameter("@FCorpID", FCorpID));
            try
            {
                DBHelper.ExeSQL(strSql.ToString(), ltpara.ToArray(), DBHelper.ConnMaster);
                return true;
            }
            catch (Exception)
            {
                return false;
                throw;
            }
        }

        public static bool SetSoftwareService(ArrayList rows, string order_no, int mCorpID, string transactionId, int years)
        {
            try
            {
                long mFPayID = 0;
                if (!ExistsNoPayItem(order_no, mCorpID, ref mFPayID)) return false;
                string connstr = SystemInfo.GetClusterConn(mCorpID);
                List<DBHelper.CommandInfo> commandlist = new List<DBHelper.CommandInfo>();
                string sql = string.Format(@"declare @time datetime
                        select @time=case when FEndTime<=getdate() then getdate() else FEndTime end from t_corpration where FItemID={4}
                        update y_iPay set FPay=1,FPayID='{1}',FPayTime=getdate() where FItemID={0}
                        update t_corpration set FEndTime=dateadd({2},{3},@time) where FItemID={4}
                        ", mFPayID, transactionId, "year", years, mCorpID);
                DBHelper.CommandInfo info = new DBHelper.CommandInfo(sql, null);
                commandlist.Add(info);
                //DBHelper.ExeSQL(sql);

                sql = @"declare @FDetailID bigint  declare @endtime datetime
                        exec dbo.pr_bi_GetMaxAutoID 't_CorpProductDetail','FDetailID',1,@FDetailID out 
                        select @endtime= case when isnull(max(FEndDate),getdate())<=getdate() then getdate() else max(FEndDate) end 
                        from t_CorpProduct where FCorpID=@FCorpID and FProductID=@FProductID 
                        insert into t_CorpProductDetail(FDetailID,FCorpID,FPayID,FProductID,FDate,FEndDate) 
                        values(@FDetailID,@FCorpID,@FPayID,@FProductID,getdate(),dateadd({0},@FTime,@endtime) )                            
                        update t_CorpProduct set FDetailID=@FDetailID,FEndDate=dateadd({0},@FTime,@endtime) 
                        where FCorpID=@FCorpID and FProductID=@FProductID ";
                string msql = "";

                foreach (Hashtable item in rows)
                {
                    if (CommonFun.ConvertToBoolean(item["checked"]))
                    {
                        int FItemID = CommonFun.ConvertToInt(item["FItemID"]);
                        SqlParameterList par_list = new SqlParameterList();
                        par_list.Add("@FCorpID", SqlDbType.Int, mCorpID);
                        par_list.Add("@FPayID", SqlDbType.BigInt, mFPayID);
                        par_list.Add("@FProductID", SqlDbType.BigInt, FItemID);
                        par_list.Add("@FTime", SqlDbType.Int, years);
                        sql = string.Format(sql, "year");
                        DBHelper.CommandInfo info1 = new DBHelper.CommandInfo(sql, par_list.Get_List());
                        commandlist.Add(info1);

                        if (FItemID == 2 || FItemID == 1 || FItemID == 4 || FItemID == 7)
                        {
                            msql = "insert into t_POSTerminal(FCorpID,FCreateTime,FEndTime) "
                                + "values(@FCorpID,getdate(),dateadd({0},@year,getdate()))";
                            msql = string.Format(msql, "year");
                            par_list = new SqlParameterList();
                            par_list.Add("@FCorpID", SqlDbType.Int, mCorpID);
                            par_list.Add("@year", SqlDbType.Int, years);
                            info = new DBHelper.CommandInfo(msql, par_list.Get_List());
                            commandlist.Add(info);
                        }
                        if (FItemID == 6)
                        {
                            msql = string.Format(@"insert into t_POSTerminal(FCorpID,FCreateTime,FEndTime,FTerminalNo) 
                                        values(@FCorpID,getdate(),dateadd({0},@year,getdate()),'MobileTerminal')", "year");
                            par_list = new SqlParameterList();
                            par_list.Add("@FCorpID", SqlDbType.Int, mCorpID);
                            par_list.Add("@year", SqlDbType.Int, years);
                            info = new DBHelper.CommandInfo(msql, par_list.Get_List());
                            commandlist.Add(info);
                        }
                        if (FItemID == 24)
                        {
                            msql = string.Format(@"insert into t_POSTerminal(FCorpID,FCreateTime,FEndTime,FTerminalNo) 
                                        values(@FCorpID,getdate(),dateadd({0},@year,getdate()),'MarketingTerminal')", "year");
                            par_list = new SqlParameterList();
                            par_list.Add("@FCorpID", SqlDbType.Int, mCorpID);
                            par_list.Add("@year", SqlDbType.Int, years);
                            info = new DBHelper.CommandInfo(msql, par_list.Get_List());
                            commandlist.Add(info);
                        }
                        if (FItemID == 25)
                        {
                            msql = string.Format(@"insert into t_POSTerminal(FCorpID,FCreateTime,FEndTime,FTerminalNo) 
                                        values(@FCorpID,getdate(),dateadd({0},@year,getdate()),'AndroidTerminal')", "year");
                            par_list = new SqlParameterList();
                            par_list.Add("@FCorpID", SqlDbType.Int, mCorpID);
                            par_list.Add("@year", SqlDbType.Int, years);
                            info = new DBHelper.CommandInfo(msql, par_list.Get_List());
                            commandlist.Add(info);
                        }
                    }
                }
                msql = @"exec pr_bi_SetCorpProdcutAuto @FCorpID";
                SqlParameterList par_list2 = new SqlParameterList();
                par_list2.Add("@FCorpID", SqlDbType.Int, mCorpID);
                DBHelper.CommandInfo info2 = new DBHelper.CommandInfo(msql, par_list2.Get_List());
                commandlist.Add(info2);
                DBHelper.ExecuteSqlTran(commandlist, connstr);
            }
            catch (Exception e)
            {
                //WxPayAPI.Log.Info("SetSoftwareService", e.Message);
                return false;
            }
            return true;
        }


        private static bool ExistsNoPayItem(string order_no, int mCorpID, ref long mPayID)
        {
            string sql = "select FItemID from y_ipay where FBillNo=@FBillNo and FCorpID =@FCorpID and FPay=0";
            string connstr = SystemInfo.GetClusterConn(mCorpID);
            SqlParameterList par_list = new SqlParameterList();
            par_list.Add("@FBillNo", SqlDbType.VarChar, order_no);
            par_list.Add("@FCorpID", SqlDbType.Int, mCorpID);
            DataTable dt = DBHelper.GetDataTable(sql, par_list.Get_List(), connstr);
            if (dt == null || dt.Rows.Count == 0) return false;
            if (dt.Rows.Count == 1)
            {
                mPayID = CommonFun.ConvertToBigInt(dt.Rows[0]["FItemID"]);
                return true;
            }
            else return false;
        }


    }
}
